Difference Between SQL Stored Procedures and Functions

Stored procedures and functions serve different purposes and have many differences in terms of their usage, capabilities, and execution.

SQL Stored Procedures

purpose

• Stored procedures are specifically designed to perform one or more tasks or operations in the database.
• They can create complex SQL queries, procedural logic (conditional loops), and other programming constructs.
• It is commonly used to perform data manipulation (INSERT, UPDATE, DELETE), data retrieval, and business logic applications.

Usage

• Stored procedures can be called independently or from SQL statements or other programs.
• You can accept input parameters and return multiple result sets.

Transactions

• Transactions can be started and executed, allowing multiple SQL functions to be compiled and executed atomically.

Security

• They provide better authorization control because they can grant direct access to the stored method to control access to the underlying data.

Execution

• The EXECUTE or EXEC command is used to execute a Stored Procedure in SQL Server.

Syntax-

The SQL CREATE PROCEDURE/PROC statement is used to create a new Stored Procedure in SQL Server, and ALTER PROCEDURE/PROC statement is used to Alter or Update the existing procedures,

USE DatabaseName
GO
CREATE/ALTER PROCEDURE Proc_ProcedureName
(
-- Parameter name if need
)AS
BEGIN 
-- SQL statement for perform in stored procedure
END

Example-

Here is a stored procedure that returns the data of a user from the Users table by user ID,

USE MyCollegeDb
GO
CREATE PROCEDURE Proc_ProcedureName
(
@UserID BIGINT 
)AS
BEGIN 
SET NOCOUNT ON;
 -- SQL statement that returns the user details based on parameter value
 SELECT * FROM Users WHERE UserID = @UserID
SET NOCOUNT OFF;
END

Now, click on Execute the above SQL statement from the SSMS Header or press F5 from the keyboard to create the Stored Procedure,

Let's execute the created stored procedure,

SQL Functions

purpose

• The functions are designed to return a single value or a table.
• It is used to contain reusable logic that calculates and returns scalar values ​​or table results based on input parameters.

Usage

• Functions are often used in SELECT statements, WHERE clauses, JOIN conditions, and other areas where expressions are allowed.

Return Types

• A value must be returned and DML operations (INSERT, UPDATE, DELETE) cannot be performed directly on the database.

Transactions

• Functions do not support explicit transaction control like stored procedures.

Security
Permissions are derived from referenced objects, and permissions should be granted to underlying objects (tables, views) rather than directly to the user

Execution
Scalar functions in SQL statements are often called inline, while table-valued functions can be used like tables in SQL queries.

Example-

The below SQL Function takes two argument values BirthDate and UserID based on the Users table and returns the user Age,

USE MyCollegeDb
GO
CREATE FUNCTION CalculateUserAge(@BirthDate DATE, @UserID INT)
RETURNS INT
AS
BEGIN
DECLARE @Age INT;
   SET @Age = (SELECT DATEDIFF(YEAR, @BirthDate, GETDATE()) AS Age FROM Users WHERE UserID = @UserID);
RETURN @Age;
END;

Execute the SQL statement above to create a function.

Let's execute the function above,

Choosing a stored method and a function depends on whether you need to perform data transformations and complex logic (using stored procedures) or calculation of return values ​​(using functions). Typically, both are used together to access application logic components in a SQL Server database.

Also, Read: Explain the SQL triggers and their uses